﻿using System;
using System.Data;
using System.Data.SQLite;
using Common.DataBase.Interface;
using Common.DataBase.Implement;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using CommonUtils.Attributes;
//using Maticsoft.DBUtility;//Please add references

namespace ScanFile.Dal{

  public partial class BaseDal<T> where T : new() {

    /// <summary>
    /// 数据库操作引用
    /// </summary>
    protected static ISQLHelper _sqlHelper = null;

    /// <summary>
    ///日志处理引用
    /// </summary>
    protected static log4net.ILog _logger = null;


    /// <summary>
    /// 表名
    /// </summary>
    protected static string tableName = string.Empty;


    /// <summary>
    /// 构造函数
    /// </summary>
    public BaseDal() {
      if (_logger == null) {
        _logger = log4net.LogManager.GetLogger(this.GetType());
      }
    }

    /// <summary>
    /// 静态构造函数
    /// </summary>
    static BaseDal() {
      _sqlHelper = new MySqlHelper();
      Object[] tableAttributes = typeof(T).GetCustomAttributes(typeof(DBTable), false);
      if (tableAttributes != null && tableAttributes.GetLength(0) != 0) {
        DBTable dbTable = (DBTable)tableAttributes[0];//获取属性类的第一个成员
        tableName = dbTable.TableName;
      }        
      insertSQL = _sqlHelper.GenerateInsertSQL(typeof(T));
      updateSQL = _sqlHelper.GenerateUpdateSQL(typeof(T));
      deleteSQL = _sqlHelper.GenerateDeleteSQL(typeof(T));
      selectSQL = _sqlHelper.GenerateSelectSQLByPrimaryKey(typeof(T));
    }

    /// <summary>
    /// 常用数据库操作语句自动生成
    /// </summary>
    public static string insertSQL = string.Empty;
    public static string updateSQL = string.Empty;
    public static string deleteSQL = string.Empty;
    public static string selectSQL = string.Empty;


    /// <summary>
    /// 构造SQL参数
    /// </summary>
    /// <param name="parameterName">参数名</param>
    /// <param name="value">参数值</param>
    /// <returns>参数对象</returns>
    protected static DbParameter DbParam(string parameterName, object value) {
      return _sqlHelper.DbParam(parameterName,value);
    }
    

    /// <summary>
    /// 插入/修改对象时，将所有属性生成参数
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <returns></returns>
    private DbParameter[] ConstructEntityParameters<T>(T t) {
      return _sqlHelper.ConstructEntityParameters<T>(t);
    }

    /// <summary>
    /// 将主键生成参数，主要用于根据主键查询
    /// 单条记录，删除单条记录
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <returns></returns>
    private DbParameter[] ConstructPrimaryKeyParameter<T>(T t) {
      return _sqlHelper.ConstructPrimaryKeyParameter<T>(t);
    }


    /// <summary>
    /// 添加单条记录
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    public System.Int64 Add(T t,bool isClose = true) {
      _logger.Debug(insertSQL);
      return _sqlHelper.ExecuteNonQuery(insertSQL, CommandType.Text, ConstructEntityParameters(t), isClose);
    }


    public System.Int64 AddList(List<T> tList) {
      System.Int64 rowCount = 0;
      try {
        foreach (T t in tList) {
          rowCount += Add(t);
        }
        return rowCount;
      } catch (Exception ex) {
        _logger.Error(ex);
      }
      return -1;
    }

    /// <summary>
    /// 插入多条记录
    /// </summary>
    /// <param name="tList"></param>
    /// <returns></returns>
    public System.Int64 AddList1(List<T> tList) {
      DbConnection connection = null;
      DbTransaction tran = null;
      System.Int64 rowCount = 0;
      try {
        connection = _sqlHelper.OpenConnection();
        tran = connection.BeginTransaction();
        foreach (T t in tList) {
          rowCount += Add(t, false);
          if (rowCount % 2 == 0 && tran != null) {
            tran.Commit();
            tran = connection.BeginTransaction();
          }
        }
        tran.Commit();
      } catch (Exception ex) {
        if (tran != null) {
          tran.Rollback();
        }
        _logger.Error(ex);
      } finally {
        if (connection != null) {
          connection.Close();
        }
      }
      return -1;
    }



    /// <summary>
    /// 根据主键修改单条记录
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    public System.Int64 UpdateById(T t, bool isClose = true) {
      _logger.Debug(updateSQL);
      return _sqlHelper.ExecuteNonQuery(updateSQL, CommandType.Text, ConstructEntityParameters(t), isClose);
    }

    /// <summary>
    /// 根据主键修改多条记录
    /// </summary>
    /// <param name="tList"></param>
    /// <returns></returns>
    public System.Int64 UpdateList(List<T> tList) {
      DbConnection connection = null;
      DbTransaction tran = null;
      System.Int64 rowCount = 0;
      try {
        connection = _sqlHelper.OpenConnection();
        tran = connection.BeginTransaction();
        foreach (T t in tList) {
          rowCount += UpdateById(t, false);
          if (rowCount % 2 == 0 && tran != null) {
            tran.Commit();
            tran = connection.BeginTransaction();
          }
        }
        tran.Commit();
      } catch (Exception ex) {
        if (tran != null) {
          tran.Rollback();
        }
        _logger.Error(ex);
      } finally {
        if (connection != null) {
          connection.Close();
        }
      }
      return -1;
    }



    /// <summary>
    /// 根据主键删除单条记录
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    public System.Int64 DeleteById(T t, bool isClose = true) {
      _logger.Debug(deleteSQL);
      return _sqlHelper.ExecuteNonQuery(deleteSQL, CommandType.Text, ConstructPrimaryKeyParameter(t), isClose);
    }

    /// <summary>
    /// 根据主键删除多条记录
    /// </summary>
    /// <param name="tList"></param>
    /// <returns></returns>
    public System.Int64 DeleteList(List<T> tList) {
      DbConnection connection = null;
      DbTransaction tran = null;
      System.Int64 rowCount = 0;
      try {
        connection = _sqlHelper.OpenConnection();
        tran = connection.BeginTransaction();
        foreach (T t in tList) {
          rowCount += DeleteById(t, false);
          if (rowCount % 2 == 0 && tran != null) {
            tran.Commit();
            tran = connection.BeginTransaction();
          }
        }
        tran.Commit();
      } catch (Exception ex) {
        if (tran != null) {
          tran.Rollback();
        }
        _logger.Error(ex);
      } finally {
        if (connection != null) {
          connection.Close();
        }
      }
      return -1;
    }

    
    /// <summary>
    /// 查找单条记录
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    public T FindByPrimary(T t) {
      _logger.Debug(deleteSQL);
      List<T> results = _sqlHelper.FindList<T>(selectSQL, CommandType.Text, ConstructPrimaryKeyParameter(t)); 
      return results == null || results.Count <=0 ? default(T): results[0];
    }


    /// <summary>
    /// 获取所有对象
    /// </summary>
    /// <returns>对象集合</returns>
    public List<T> GetAll() {
      string sqlString = string.Format("SELECT * FROM {0} ;", tableName);
      return GetList(sqlString);
    }


    /// <summary>
    /// 根据SQL获取对象集合
    /// </summary>
    /// <param name="sqlString">查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>对象集合</returns>
    public List<T> GetList(string sqlString, DbParameter[] parameters=null) {
      return _sqlHelper.FindList<T>(sqlString, CommandType.Text, parameters);
    }

    /// <summary>
    /// 根据SQL获取单个对象
    /// </summary>
    /// <param name="sqlString">查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>对象集合</returns>
    public T FindBySQL(string sqlString, DbParameter[] parameters = null) {
      List<T> listT = GetList(sqlString,parameters);
      return listT == null || listT.Count <= 0?default(T):listT[0];
    }

  }
}
